package org.noip.milhous.clause;

import java.util.Arrays;
import java.util.LinkedList;
import java.util.List;

import org.noip.milhous.SQLInterface;

/**
 * A SQL where clause. This class limits the number of objects that will be retrieved from the database when a query is made to the database
 * 
 * @author millermj
 * 
 */
public class Where extends Utilities implements SQLInterface
{
	/**
	 * the operators that can be used in a where clause
	 * 
	 * @author millermj
	 * 
	 */
	public enum OPERATOR
	{
		AND(" AND "),
		OR(" OR "),
		EQUAL(" = "),
		GreaterThan(" > "),
		GreaterThanEQ(" >= "),
		LessThan(" < "),
		LessThanEQ(" <= "),
		NOTEQUAL(" <> "),
		LIKE(" LIKE "),
		NOTLIKE(" NOT LIKE "),
		IS(" IS "),
		ISNOT(" IS NOT "),
		IN(" IN ");
		private String op;

		private OPERATOR(String op)
			{
				this.op = op;
			}
	};

	/**
	 * put parenthesis around the clause
	 */
	private final Boolean group;

	/**
	 * right side of the statement
	 */
	private final Object right;

	/**
	 * The operator to use
	 */
	private final OPERATOR operator;

	/**
	 * The left (or single) side of the statement
	 */
	private final Object left;

	/**
	 * A list of order by
	 */
	private List<OrderBy> orderByList;

	public static Where equal(Object left, OrderBy... order)
		{
			return new Where(left, OPERATOR.EQUAL, "?", order);
		}

	public static Where equalOrderBy(Object left, OrderBy... order)
		{
			return new Where(left, OPERATOR.EQUAL, "?", order);
		}

	/**
	 * Create a new Where statement where the operator is the Equality operator
	 * 
	 * @param left
	 *          the column on the left hand side of the equailty statement
	 * @return a new Where clause
	 */
	public static Where equal(Object left)
		{
			return new Where(left, OPERATOR.EQUAL, "?");
		}

	/**
	 * Create a new Where clause where the operator is the Greater than operator
	 * 
	 * @param left
	 *          the column on the left hand side of the statement
	 * @return a new Where clause
	 */
	public static Where greaterThan(Object left)

		{
			return new Where(left, OPERATOR.GreaterThan, "?");
		}

	public static Where lessThan(Object left)
		{
			return new Where(left, OPERATOR.LessThan, "?");
		}

	public static Where like(Object left)
		{
			return new Where(left, OPERATOR.LIKE, "?");
		}

	/**
	 * Create a Where statement with the clauses anded together
	 * 
	 * @param items
	 *          the list of items to be anded together
	 * @return a new where statement with the items anded together
	 */
	public static Where getBigAnd(Where... items)
		{
			return getBigAnd(new LinkedList<Where>(Arrays.asList(items)));
		}

	/**
	 * Create a Where statement with the clauses ored together
	 * 
	 * @param items
	 *          the list of items to be ored together
	 * @return a new where statement with the items ored together
	 */
	public static Where getBigOr(Where... items)
		{
			Where result = null;
			LinkedList<Where> whereList = new LinkedList<Where>(Arrays.asList(items));
			Where w1 = whereList.removeFirst();
			if (whereList.size() > 0)
				{
					result = new Where(w1, OPERATOR.OR, getBigOr(whereList), true);
				}
			else
				{
					result = w1;
				}

			return result;
		}

	public static Where getBigAnd(List<Where> items)
		{
			Where result = null;
			Where o = items.remove(0);
			if (items.size() > 0)
				{
					Where right = getBigAnd(items);
					result = new Where(o, OPERATOR.AND, right);
					if (right.orderByList != null && !right.orderByList.isEmpty())
						{
							result.orderByList = right.orderByList;
							right.orderByList = null;
						}
				}
			else
				{
					result = o;
				}

			return result;
		}

	public static Where getBigOr(List<Where> items)
		{
			Where result = null;
			Where o = items.remove(0);
			if (items.size() > 0)
				{
					result = new Where(o, OPERATOR.OR, getBigOr(items));
				}
			else
				{
					result = o;
				}

			return result;
		}

	/**
	 * create a new Where statement where the only argument is the orderBy
	 * 
	 * @param order
	 *          the {@link OrderBy} object to order the results by
	 */
	public Where(OrderBy order)
		{
			this(null, null, null, false, order);
		}

	public Where(Object left, OrderBy... order)
		{
			this(left, null, null, false, order);
		}

	public Where(Object left, Boolean group, OrderBy... order)
		{
			this(left, null, null, group, order);
		}

	public Where(Object left, OPERATOR operator, Object right, OrderBy... order)
		{
			this(left, operator, right, false, order);
		}

	public Where(Object left, OPERATOR operator, Object right, boolean group, OrderBy... order)
		{
			super();
			this.group = group;
			this.right = right;
			this.operator = operator;
			this.left = left;
			setOrderBies(order);
		}

	private void setOrderBies(OrderBy... orderBies)
		{
			if (orderBies != null && orderBies.length > 0)
				{
					orderByList = new LinkedList<OrderBy>();
					for (OrderBy o : orderBies)
						{
							orderByList.add(o);
						}
				}
		}

	/**
	 * Use this when this is part of a bigger where clause
	 */
	public String toInternalSQL()
		{
			StringBuffer result = new StringBuffer();
			if (group)
				{
					result.append("(");
				}
			if (left != null)
				{
					result.append(getInternal(left));
				}
			if (operator != null) // if the op is null, then we are using just one clause
				{
					result.append(operator.op);
					result.append(getInternal(right));
				}
			if (group)
				{
					result.append(")");
				}
			return result.toString();
		}

	/**
	 * Get the full sql for this where clause
	 */
	public String toSQL()
		{
			StringBuffer result = new StringBuffer();
			String internal = toInternalSQL();
			if (!internal.equals(""))
				{
					result.append(" WHERE " + internal);
				}

			if (orderByList != null && orderByList.size() > 0)
				{
					List<OrderBy> orders = new LinkedList<OrderBy>(orderByList);
					result.append(orders.remove(0).toSQL()); // take the first one off
					for (OrderBy o : orders) // then do the rest
						{
							result.append("," + o.toInternalSQL());
						}
				}
			return result.toString();
		}

	/**
	 * String representation of this where clause
	 */
	@Override
	public String toString()
		{
			return toInternalSQL();
		}

	public static void main(String[] aaa)
		{
			Where w = new Where("a", OPERATOR.EQUAL, "b");
			Where x = new Where("a", OPERATOR.EQUAL, "c");
			Where y = new Where(x, OPERATOR.AND, w, true);
			Where z = new Where("q", OPERATOR.EQUAL, "s");
			Where a = new Where(z, OPERATOR.OR, y, true, new OrderBy("c", true), new OrderBy("x", false));
			System.out.println(a.toSQL());
			a = new Where(z, OPERATOR.OR, new NotNull("zz"), true, new OrderBy("q", true));
			System.out.println(a.toSQL());
		}

}
